import sys
import xlrd
from model.University import University


# 复制2021年的院校数据到2022年
from model.UniversityDetail import UniversityDetail
from model.UniversityDocuments import UniversityDocuments


def to_copy():
    university = University()
    old_year = 2021
    new_year = 2022
    data = university.getList(old_year)
    total_list = list()
    for index, row in enumerate(data):
        print(index, row)
        row['year'] = new_year
        del row['id']
        total_list.append(row)

    university.toInsertBatch(total_list)


# 复制2021年的院校数据到2022年
def to_copy_detail():
    universityDetail = UniversityDetail()
    old_year = 2021
    new_year = 2022
    data = universityDetail.getList(old_year)
    total_list = list()
    for index, row in enumerate(data):
        print(index, row)
        row['year'] = new_year
        del row['id']
        total_list.append(row)

    universityDetail.toInsertBatch(total_list)


# 更新院校性质信：公办/民办/独立院校/精准专项
def update_university():
    data = xlrd.open_workbook('../static/excel/university20220624.xls')
    table = data.sheet_by_name('Sheet1')
    rowNum = table.nrows
    colNum = table.ncols
    print(rowNum, colNum)
    university = University()
    universityDetail = UniversityDetail()
    for i in range(rowNum):
        if i > 1:
            if table.cell(i, 3).value:
                base_row = dict()
                detail_row = dict()
                code = int(table.cell(i, 3).value)
                base_row['year'] = 2022
                base_row['code'] = code
                base_row['name'] = table.cell(i, 4).value
                base_row['level_name'] = table.cell(i, 5).value
                base_row['province'] = table.cell(i, 6).value
                base_row['university_rank'] = table.cell(i, 7).value
                base_row['university_rank_text'] = table.cell(i, 8).value
                base_row['type_name'] = table.cell(i, 9).value
                if table.cell(i, 10).value:
                    if table.cell(i, 10).value == '公办':
                        base_row['nature_name'] = 1
                    elif table.cell(i, 10).value == '民办':
                        base_row['nature_name'] = 2
                    elif table.cell(i, 10).value == '合作办学':
                        base_row['nature_name'] = 3
                    elif table.cell(i, 10).value == '独立学院':
                        base_row['nature_name'] = 4
                    else:
                        base_row['nature_name'] = 0
                base_row['change_difficult_level'] = table.cell(i, 11).value

                detail_row['year'] = 2022
                detail_row['code'] = code
                detail_row['name'] = table.cell(i, 4).value
                detail_row['special_major'] = table.cell(i, 15).value
                detail_row['change_rule'] = table.cell(i, 12).value
                detail_row['wan_plan'] = table.cell(i, 16).value
                detail_row['introduction_1'] = table.cell(i, 13).value
                detail_row['introduction_2'] = table.cell(i, 14).value
                detail_row['introduction_3'] = table.cell(i, 15).value
                detail_row['introduction_4'] = table.cell(i, 18).value
                detail_row['both_subject'] = table.cell(i, 17).value

                print(code)
                exit_base_data = university.getOneByCode(code)
                if exit_base_data:
                    print('更新base', base_row)
                    university.toUpdateBase(base_row)
                else:
                    print('添加base', base_row)
                    university.toInsert(base_row)
                exit_detail_data = universityDetail.getOneByCode(code)
                print(exit_detail_data)
                if exit_detail_data:
                    print('更新detail', detail_row)
                    universityDetail.toUpdate(detail_row)
                else:
                    print('添加detail', detail_row)
                    universityDetail.toInsert(detail_row)


# 更新院校招生章程
def update_university_documents():
    data = xlrd.open_workbook('../static/excel_2023/university_documents_2.xls')
    table = data.sheet_by_name('Sheet1')
    rowNum = table.nrows
    colNum = table.ncols
    print(rowNum, colNum)
    universityDocuments = UniversityDocuments()
    for i in range(rowNum):
        if i > 0:
            if table.cell(i, 3).value:
                base_row = dict()
                code = int(table.cell(i, 0).value)
                base_row['year'] = 2023
                base_row['code'] = code
                base_row['name'] = table.cell(i, 1).value
                base_row['rules'] = table.cell(i, 3).value
                exit_base_data = universityDocuments.getOneByCode(code)
                print(exit_base_data)
                if exit_base_data:
                    universityDocuments.toUpdate(base_row)
                else:
                    universityDocuments.toInsert(base_row)


def update_university_0624():
    data = xlrd.open_workbook('../static/excel/university20220626.xls')
    table = data.sheet_by_name('Sheet1')
    rowNum = table.nrows
    colNum = table.ncols
    print(rowNum, colNum)
    university = University()
    universityDetail = UniversityDetail()
    for i in range(rowNum):
        if i > 0:
            if table.cell(i, 3).value:
                base_row = dict()
                detail_row = dict()
                code = int(table.cell(i, 3).value)
                base_row['year'] = 2022
                base_row['code'] = code
                base_row['name'] = table.cell(i, 4).value
                base_row['level_name'] = table.cell(i, 5).value
                base_row['province'] = table.cell(i, 6).value
                base_row['university_rank'] = table.cell(i, 7).value
                base_row['university_rank_text'] = table.cell(i, 8).value
                base_row['type_name'] = table.cell(i, 9).value
                if table.cell(i, 10).value:
                    if table.cell(i, 10).value == '公办':
                        base_row['nature_name'] = 1
                    elif table.cell(i, 10).value == '民办':
                        base_row['nature_name'] = 2
                    elif table.cell(i, 10).value == '合作办学':
                        base_row['nature_name'] = 3
                    elif table.cell(i, 10).value == '独立学院':
                        base_row['nature_name'] = 4
                    else:
                        base_row['nature_name'] = 0
                base_row['change_difficult_level'] = table.cell(i, 11).value

                detail_row['year'] = 2022
                detail_row['code'] = code
                detail_row['name'] = table.cell(i, 4).value
                detail_row['special_major'] = table.cell(i, 15).value
                detail_row['change_rule'] = table.cell(i, 12).value
                detail_row['wan_plan'] = table.cell(i, 16).value
                detail_row['introduction_1'] = table.cell(i, 13).value
                detail_row['introduction_2'] = table.cell(i, 14).value
                detail_row['introduction_3'] = table.cell(i, 15).value
                detail_row['introduction_4'] = table.cell(i, 18).value
                detail_row['both_subject'] = table.cell(i, 17).value

                print(code)
                exit_base_data = university.getOneByCode(code)
                print(exit_base_data)
                if exit_base_data:
                    university.toUpdateBase(base_row)
                else:
                    university.toInsert(base_row)
                exit_detail_data = universityDetail.getOneByCode(code)
                print(exit_detail_data)
                if exit_detail_data:
                    universityDetail.toUpdate(detail_row)
                else:
                    universityDetail.toInsert(detail_row)


# 更新录取规则
def update_university_rule():
    data = xlrd.open_workbook('../static/excel_2023/update_university_rule_2.xls')
    table = data.sheet_by_name('Sheet1')
    rowNum = table.nrows
    colNum = table.ncols
    print(rowNum, colNum)
    university = University()
    k = 0
    for i in range(rowNum):
        if i > 0:
            if table.cell(i, 0).value:
                base_row = dict()
                code = int(table.cell(i, 0).value)
                base_row['code'] = code
                base_row['level_name'] = table.cell(i, 2).value
                base_row['accept_add'] = table.cell(i, 3).value
                base_row['enter_rule'] = table.cell(i, 4).value
                base_row['jicha_2'] = table.cell(i, 5).value
                base_row['jicha_3'] = table.cell(i, 6).value
                base_row['jicha_4'] = table.cell(i, 7).value
                base_row['jicha_5'] = table.cell(i, 8).value
                base_row['jicha_6'] = table.cell(i, 9).value
                print(code)
                exit_base_data = university.getOneByCode(code)
                print(base_row)
                if exit_base_data:
                    k = k + 1
                    university.toUpdateRule(base_row)
    print('k:', k)


# 更新转专业规则和难度
def update_university_change_rule():
    data = xlrd.open_workbook('../static/excel_2023/change_rule.xls')
    table = data.sheet_by_name('Sheet1')
    rowNum = table.nrows
    colNum = table.ncols
    print(rowNum, colNum)
    university = University()
    universityDetail = UniversityDetail()
    for i in range(rowNum):
        if i > 0:
            if table.cell(i, 3).value:
                base_row = dict()
                detail_row = dict()
                code = int(table.cell(i, 0).value)
                base_row['code'] = code
                base_row['change_difficult_level'] = table.cell(i, 2).value
                detail_row['code'] = code
                detail_row['change_rule'] = table.cell(i, 3).value
                print(code)
                exit_base_data = university.getOneByCode(code)
                print(exit_base_data)
                if exit_base_data:
                    university.toUpdateBase2(base_row)

                exit_detail_data = universityDetail.getOneByCode(code)
                print(exit_detail_data)
                if exit_detail_data:
                    universityDetail.toUpdate2(detail_row)


if __name__ == "__main__":
    update_university_documents()